import { query } from '../config/database.js'

export const createMatchPlayersTable = async () => {
  const sql = `
    CREATE TABLE IF NOT EXISTS match_players (
      id INT AUTO_INCREMENT PRIMARY KEY,
      match_id INT NOT NULL,
      player_id INT NOT NULL,
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      FOREIGN KEY (match_id) REFERENCES matches(id),
      FOREIGN KEY (player_id) REFERENCES players(id)
    )
  `
  
  try {
    await query(sql)
    console.log('Match players table created or already exists')
  } catch (error) {
    console.error('Error creating match players table:', error)
    throw error
  }
}

export const addPlayerToMatch = async (matchId, playerId) => {
  const sql = 'INSERT INTO match_players (match_id, player_id) VALUES (?, ?)'
  try {
    const result = await query(sql, [matchId, playerId])
    return result.insertId
  } catch (error) {
    console.error('Error adding player to match:', error)
    throw error
  }
}

export const getMatchPlayers = async (matchId) => {
  const sql = `
    SELECT p.* 
    FROM players p
    JOIN match_players mp ON p.id = mp.player_id
    WHERE mp.match_id = ?
  `
  try {
    return await query(sql, [matchId])
  } catch (error) {
    console.error('Error getting match players:', error)
    throw error
  }
} 